package com.xiedafeng.www.util;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;

public class InsertUtil<T,E> {
    /*
    某个对象（类）对应的数据表，insert后返回主键id值，方便关联表操作
     */
    public int insert(T t) throws Exception {
        int count,id=0;
        //sql1是为了先执行一下简单的查询，这样做当然是为了通过getMethod获取对象的成员变量的getter和setter方法并执行了，sql2才是主角
        String searchSql="select * from "+t.getClass().getSimpleName();
        StringBuilder insertSql= new StringBuilder("insert into " + t.getClass().getSimpleName() + " values(");
        DataSource dataSource=new DataSourceUtil();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet = null;
        ResultSetMetaData resultSetMetaData;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(searchSql);
            resultSet = preparedStatement.executeQuery();
            resultSetMetaData = resultSet.getMetaData();
            count=resultSetMetaData.getColumnCount();
            for (int i = 1; i < count; i++) {
                insertSql.append("?,");
            }
            insertSql.append("?)");
            preparedStatement=connection.prepareStatement(insertSql.toString());
            preparedStatement.setObject(1,null);
            for (int i = 1; i < count; i++) {
                Method method=t.getClass().getMethod("get"+resultSetMetaData.getColumnName(i+1));
                preparedStatement.setObject(i+1,method.invoke(t));
            }
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(searchSql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){id=resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            JdbcUtil.close(resultSet, preparedStatement);
        }
        return id;
    }
    /*
    insert数据到关联表中，t为主动寻求关联的一方，e被关联（和search那里一样，这个方法对应于search中参数x为null的情况）
     */
    public int insert(T t,E e,int id1,int id2) throws SQLException {
        int result=0;
        String sql="insert into "+t.getClass().getSimpleName()+e.getClass().getSimpleName()+" values(?,?)";
        DataSource dataSource=new DataSourceUtil();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection = dataSource.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id1);
            preparedStatement.setInt(2,id2);
            result=preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            connection.close();
            JdbcUtil.close(preparedStatement);
        }
        return result;
    }
    /*
    insert数据到关联表中，tid为主动寻求关联的一方的id，e被关联（和search那里一样，这个方法对应于search中参数x不为null的情况）
     */
    public int insert(T t,int tid,int eid) throws Exception {
        int result=0;
        String selectSql="select * from "+t.getClass().getSimpleName();
        StringBuilder insertSql= new StringBuilder("insert into " + t.getClass().getSimpleName() + " values(?,?");
        DataSource dataSource=new DataSourceUtil();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet = null;
        ResultSetMetaData resultSetMetaData;
        Field[] fields=t.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            insertSql.append(",?");
        }
        insertSql.append(")");
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(selectSql);
            resultSet = preparedStatement.executeQuery();
            resultSetMetaData = resultSet.getMetaData();
            preparedStatement=connection.prepareStatement(insertSql.toString());
            preparedStatement.setInt(1,tid);
            preparedStatement.setInt(2,eid);
            for (int i = 0; i < fields.length; i++) {
                Method method=t.getClass().getMethod("get"+resultSetMetaData.getColumnName(i+3));
                preparedStatement.setObject(i+3,method.invoke(t));
            }
            result=preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            connection.close();
            JdbcUtil.close(resultSet,preparedStatement);
        }
        return result;
    }
}
